﻿Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class frm_schedule
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Dim models As New BindingSource
    Dim thisMonth, thisYear, DaysInMonth As Integer
    Dim nSheets As Integer
    Dim selectedSheet As Integer

    Dim sched(5, 31) As String
    Dim sname(5) As String
    

    Private Sub frm_schedule_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        If Not xlApp Is Nothing Then
            CloseExcel()
        End If
    End Sub


    Private Sub frm_schedule_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        init()
        'OpenExcel()

        ''select list of model
        'GetListModels()

    End Sub

    Private Sub init()
        'Init
        If Not xlApp Is Nothing Then
            CloseExcel()
        End If

        listSheet.Items.Clear()
        ListShop.Items.Clear()
        ListShop.Items.Add("Body Shop (BDY)")
        ListShop.Items.Add("Painting Shop (PNT)")
        ListShop.Items.Add("Trimming Shop (TRI)")
        ListShop.Items.Add("Chassis Shop (CHA)")
        ListShop.Items.Add("Finishing Shop (FNG)")

        btn_select.Enabled = False
        btn_download.Enabled = False

        Dim i, j As Integer
        For i = 0 To 5
            sname(i) = ""
            For j = 0 To 31
                sched(i, j) = ""
            Next
        Next

    End Sub

    Private Sub GetListModels()
        models = New BindingSource
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()
            Dim sql = "SELECT DISTINCT ModelID FROM Cycle"

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read
                models.Add(reader.GetString(0))
            End While
            reader.Close()
            cmd.Dispose()
        End Using
    End Sub

    Private Sub LoadDataFromSheet(s As Integer)
        
        xlWorkSheet = xlWorkBook.Worksheets(s)

        Dim shopName As String = xlWorkSheet.Cells(4, 1).value 'NOTE: row first, col later
        'Dim day As Integer = xlWorkSheet.Cells(2, 9).value
        'Dim month As String = xlWorkSheet.Cells(1, 7).value
        Dim firstRow As Integer = 4
        Dim lastRow As Integer = firstRow
        Dim col As Integer = 7
        Dim val As String
        Dim n As Integer = 0
        'Dim objRange As Excel.Range = xlWorkSheet.Range("A1", "AZ500")

        sname(n) = shopName.Trim.Replace(" ", "").ToUpper
        For n = 0 To 4 'must be always has 5 shops
            'look at col D, until empty then lastRow
            While (True)

                val = xlWorkSheet.Range("D" & lastRow.ToString).Value2
                If val = "" Then
                    Exit While
                Else
                    lastRow += 1
                End If
                'Console.WriteLine("Cells " & lastRow & ", 4: " & val)
                'MessageBox.Show("Cells " & lastRow & ", 4: " & val)
            End While
            lastRow -= 1

            MessageBox.Show(shopName & " " & firstRow & " " & lastRow)

            Dim r, c As Integer
            Dim plan As String = ""

            For c = col To col + DaysInMonth - 1
                plan = ""
                For r = lastRow To firstRow Step -1
                    If ((xlWorkSheet.Cells(r, c).text <> "") AndAlso (xlWorkSheet.Cells(r, 2).text <> "")) Then
                        plan &= " " & (xlWorkSheet.Cells(r, 2).text & "(" & xlWorkSheet.Cells(r, c).text & ")")
                    End If
                Next
                sched(n, c - col + 1) = plan
            Next

        Next

    End Sub

    Private Sub OpenExcelFile(fileName As String)

        Dim i As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(fileName, , True)
        nSheets = xlWorkBook.Worksheets.Count

        For i = 1 To nSheets
            If DirectCast(xlWorkBook.Worksheets(i), Excel.Worksheet).Visible Then
                listSheet.Items.Add(xlWorkBook.Worksheets(i).Name)
            Else
                listSheet.Items.Add(xlWorkBook.Worksheets(i).Name & " (*)")
            End If
        Next

        xlWorkSheet = xlWorkBook.Worksheets(nSheets)

        Dim shopName As String = xlWorkSheet.Cells(4, 1).value 'NOTE: row first, col later
        Dim day As Integer = xlWorkSheet.Cells(2, 9).value
        Dim month As String = xlWorkSheet.Cells(1, 7).value
        thisMonth = ConvertMonth2Int(month)

        Dim year As String = xlWorkSheet.Cells(1, 1).value
        Dim p As Integer = year.IndexOf("201")
        If p >= 0 Then
            thisYear = year.Substring(p, 4)
        Else
            thisYear = Today.Year
        End If
        DaysInMonth = Date.DaysInMonth(thisYear, thisMonth)

        lbl_nSheets.Text = "This file contains " & nSheets & " sheet(s)."
        lbl_file.Text = fileName.Remove(0, fileName.LastIndexOf("\") + 1)
        lbl_month.Text = thisMonth & "/" & thisYear

        lbl_plsSelect.Visible = (nSheets > 1)
        btn_select.Enabled = (nSheets > 1)

        'load the data grid view
        With data1
            .RowCount = DaysInMonth
            For i = 0 To DaysInMonth - 1
                .Rows(i).Cells(0).Value = (New Date(thisYear, thisMonth, i + 1)).ToShortDateString
            Next
        End With



        If nSheets = 1 Then
            selectedSheet = 1
            listSheet.SelectedIndex = 0
            LoadDataFromSheet(selectedSheet)
        End If

        'CloseExcel()
    End Sub

    Private Sub CloseExcel()
        'clean up and quit
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
        xlWorkSheet = Nothing
        xlWorkBook.Close(False) 'close, no save
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
        xlWorkBook = Nothing

        xlApp.Workbooks.Close()
        xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        xlApp = Nothing
    End Sub

    Private Sub btn_load_Click(sender As Object, e As EventArgs) Handles btn_load.Click
        With OpenFileDialog1
            .Title = "Open schedule file"
            .Filter = "Excel Files (*.xlsx)|*.xlsx"

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                init()

                Cursor = Cursors.WaitCursor

                OpenExcelFile(.FileName)

                Cursor = Cursors.Default

                'MonthCalendar1.MinDate = New Date(thisYear, thisMonth, 1)
                'MonthCalendar1.MaxDate = New Date(thisYear, thisMonth, Date.DaysInMonth(thisYear, thisMonth))

                
            End If
        End With
    End Sub

    Private Sub btn_select_Click(sender As Object, e As EventArgs) Handles btn_select.Click
        selectedSheet = listSheet.SelectedIndex + 1
        LoadDataFromSheet(selectedSheet)
    End Sub

    Private Sub ListShop_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListShop.SelectedIndexChanged
        Dim i As Integer = ListShop.SelectedIndex
        Dim r As Integer

        With data1
            For r = 0 To DaysInMonth - 1
                If (sched(i, r + 1) = "") Then
                    .Rows(r).Cells(1).Value = "No plan"
                Else
                    .Rows(r).Cells(1).Value = sched(i, r + 1)
                End If
            Next
        End With
    End Sub
End Class